import os
import pandas as pd
import pymysql
import cv2
from com.atzl.common.Datas import MessagesData
from com.atzl.common.StatusCode import StatusCode
from com.atzl.functions.accounting.sql import AddData
from com.atzl.common.Paths import *
from com.atzl.common.UsersMessage import MessagesUsers
from com.atzl.common.States import States
from com.atzl.functions.accounting.func.Logs import CheckLogs
from com.atzl.functions.accounting.sql.AddData import AddDataToBase
from com.atzl.functions.accounting.sql.QueryData import QueryDataByBase


class SetPrice(CheckLogs):

    # 解析表头,获取工号,买单价格,空包价格
    #   参数:
    #       filepath:Excel文件路径
    def get_header_message(self, filepath):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            rdheader = pd.read_excel(filepath)  # 读取excel
            rdheader = pd.DataFrame(rdheader)  # 将数据读入数据帧
            rdheader = rdheader.columns[0]  # 提取第一行
            message = self.analysis_table_head(rdheader)
            sql = AddDataToBase.isornotexist_table(message[0])
            cur.execute(sql)
            data = cur.fetchall()
            df = pd.DataFrame(data)
            df = df.iloc[0, 0]
            if int(df) == 0:
                return StatusCode.TABLE_ISHAVE
            # 在数据库创建报价表
            MessagesData.OBJ_ID_PRICE = message[0]
            sql0 = AddDataToBase.create_bill_table(message[0])
            cur.execute(sql0)
            # 在单价空包价格表插入数据
            sql1 = AddDataToBase.inster_data_toum(message[0], message[1], message[2], "empty", MessagesUsers.REAL_NAME)
            cur.execute(sql1)
            sql2 = AddData.rmduplicates_um()
            cur.execute(sql2)
            # 提交执行
            conn.commit()
            cur.close()
            conn.close()
            return message
        except Exception:
            cur.close()
            conn.close()
            return StatusCode.FILEFORMAT_ERROR

    # 解析字段名内容 并生成sql数据表
    def get_field_message(self, filepath):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        header_message = self.get_header_message(filepath)
        obj_id = header_message[0]
        if header_message == StatusCode.TABLE_ISHAVE:
            return StatusCode.TABLE_ISHAVE
        try:
            rdfield = pd.read_excel(filepath, header=1)  # 从第二行开始读取
            MessagesData.EXCEL_HEAD_SHPE = rdfield.shape[0]  # 将总行数存入公共模块
            rdfield = self.states_switch(rdfield)  # 省份转换
            rdcolumns = rdfield.columns  # 获取每列名

            # 不分店铺,不分账单
            line = rdfield.shape[1]  # 总列数
            row = rdfield.shape[0]  # 总行数
            self.states = rdfield[rdcolumns[0]]  # 省份系列
            self.head_price = rdfield[rdcolumns[1]]  # 首重价格序列
            for k, o in zip(rdcolumns, range(line)):
                if o == 1:  # 首重重量
                    self.head_kg_sql = self.fixed_analysis_head(k)  # 准备插入sql, 首重重量
                    for i in range(row):
                        sql = AddDataToBase.inster_data_tobill(obj_id,  self.states[i], self.head_kg_sql, 0,
                                                               self.head_kg_sql, self.head_price[i],
                                                               0, 0, 1, 1, 'empty')
                        cur.execute(sql)
                        conn.commit()
                elif o == line - 3:  # 续重
                    cte = self.analysis_cte(k)
                    self.cte_kg_sql = cte[0]  # 准备插入sql, 续重阶段最小值
                    self.cte_unit_sql = cte[1]  # 准备插入sql, 续重阶段计算单位
                    self.cte_carry_sql = cte[2]  # 准备插入sql, 续重阶段进位
                    for i, y in zip(range(row), rdfield[k]):
                        cte_text = rdfield.columns[o - 1]  # 续重首重字段名
                        self.cte_head_kg_sql = self.analysis_cte_head(cte_text)  # 准备插入sql, 续重首重重量
                        self.cte_head_price_sql = rdfield[cte_text]  # 续重首重价格
                        sql = AddDataToBase.inster_data_tobill(obj_id,  self.states[i], self.cte_head_kg_sql,
                                                               self.cte_kg_sql, 50, self.cte_head_price_sql[i], y, 0,
                                                               self.cte_unit_sql, self.cte_carry_sql, 'empty')
                        cur.execute(sql)
                        conn.commit()

                elif 1 < o < (line - 4) and k.find('-') == -1:  # 固定阶段
                    self.this_fiexd_max_sql = self.find_take_head_kg(k)  # 准备插入sql, 当前固定阶段最大重量
                    self.this_fiexd_min_sql = self.find_take_head_kg(rdcolumns[o - 1])  # 准备插入sql, 当前固定阶段最小重量
                    for i, y in zip(range(row), rdfield[k]):
                        sql = AddDataToBase.inster_data_tobill(obj_id,  self.states[i], self.head_kg_sql,
                                                               self.this_fiexd_min_sql, self.this_fiexd_max_sql,
                                                               0, 0, y, 1, 1, 'empty')
                        cur.execute(sql)
                        conn.commit()
                elif 1 < o < (line - 4) and k.find('-') > 0:  # 浮动阶段
                    this_float = self.float_cte_price(k)
                    self.this_float_max_sql = this_float[0]  # 准备插入sql, 浮动阶段最大值
                    self.this_float_min_sql = this_float[1]  # 准备插入sql, 浮动阶段最小值
                    self.this_float_unit_sql = this_float[2]  # 准备插入sql, 浮动阶段计算单位
                    self.this_float_carry_sql = this_float[3]  # 准备插入sql, 浮动阶段进位
                    for i, y in zip(range(row), rdfield[k]):
                        sql = AddDataToBase.inster_data_tobill(obj_id,  self.states[i], self.head_kg_sql,
                                                               self.this_float_min_sql, self.this_float_max_sql,
                                                               self.head_price[i], y, 0, self.this_float_unit_sql,
                                                               self.this_float_carry_sql, 'empty')
                        cur.execute(sql)
                        conn.commit()
            nw_text = rdfield['备注'][0]
            MessagesData.NW_TEXT = nw_text
            if nw_text != '无':
                sql_creatnw = AddDataToBase.creat_nwprice_table(obj_id)
                cur.execute(sql_creatnw)
                remark = self.analysis_nw(nw_text)
                proportion = str(float(remark[0]) * 0.01)
                price = remark[1]
                sql = AddDataToBase.inster_data_tonwbill(obj_id, proportion, self.head_kg_sql, 0, 50,
                                                         price, 0, 0, 1, 1, 'empty')
                self.creat_nw_index(conn, header_message[0])
                cur.execute(sql)
                conn.commit()
            self.creat_index(conn, header_message[0])
            cur.close()
            conn.close()
            return self.get_price(str(header_message[0]))
        except Exception:
            cur.close()
            conn.close()
            self.del_price_table(MessagesData.OBJ_ID_PRICE)
            return StatusCode.OPERATION_LOSE



    # 创建报价字段索引
    def creat_index(self, conn, obj):
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sqlid = AddDataToBase.create_id_index(obj)
        sqlobjid = AddDataToBase.create_objid_index(obj)
        sqlstates = AddDataToBase.create_states_index(obj)
        sqlheadkg = AddDataToBase.create_headkg_index(obj)
        sqlstagemin = AddDataToBase.create_stagemin_index(obj)
        sqlstagemax = AddDataToBase.create_stagemax_index(obj)
        sqlheadprice = AddDataToBase.create_headprice_index(obj)
        sqlctekg = AddDataToBase.create_ctekg_index(obj)
        sqlctefixed = AddDataToBase.create_ctefixed_index(obj)
        sqlcteunit = AddDataToBase.create_cteunit_index(obj)
        sqlcarry = AddDataToBase.create_carry_index(obj)
        sqlstore = AddDataToBase.create_store_index(obj)
        cur.execute(sqlid)
        cur.execute(sqlobjid)
        cur.execute(sqlstates)
        cur.execute(sqlheadkg)
        cur.execute(sqlstagemin)
        cur.execute(sqlstagemax)
        cur.execute(sqlheadprice)
        cur.execute(sqlctekg)
        cur.execute(sqlctefixed)
        cur.execute(sqlcteunit)
        cur.execute(sqlcarry)
        cur.execute(sqlstore)
        conn.commit()
        cur.close()


    # 创建报价字段索引
    def creat_nw_index(self, conn, obj):
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sqlid = AddDataToBase.create_nwid_index(obj)
        sqlobjid = AddDataToBase.create_nwobjid_index(obj)
        sqlproportion = AddDataToBase.create_proportion_index(obj)
        sqlheadkg = AddDataToBase.create_nwheadkg_index(obj)
        sqlstagemin = AddDataToBase.create_nwstagemin_index(obj)
        sqlstagemax = AddDataToBase.create_nwstagemax_index(obj)
        sqlheadprice = AddDataToBase.create_nwheadprice_index(obj)
        sqlctekg = AddDataToBase.create_nwctekg_index(obj)
        sqlctefixed = AddDataToBase.create_nwctefixed_index(obj)
        sqlcteunit = AddDataToBase.create_nwcteunit_index(obj)
        sqlcarry = AddDataToBase.create_nwcarry_index(obj)
        sqlstore = AddDataToBase.create_nwstore_index(obj)
        cur.execute(sqlid)
        cur.execute(sqlobjid)
        cur.execute(sqlproportion)
        cur.execute(sqlheadkg)
        cur.execute(sqlstagemin)
        cur.execute(sqlstagemax)
        cur.execute(sqlheadprice)
        cur.execute(sqlctekg)
        cur.execute(sqlctefixed)
        cur.execute(sqlcteunit)
        cur.execute(sqlcarry)
        cur.execute(sqlstore)
        conn.commit()
        cur.close()



    # 解析表头
    def analysis_table_head(self, head_text):
        obj_id = self.find_obj_id(head_text)
        empty_price = self.find_empty_price(head_text)
        by_price = self.find_by_price(head_text)
        return obj_id, empty_price, by_price

    # 找工号
    def find_obj_id(self, head_text):
        empty_index = self.find_enpty_index(head_text)
        obj_id = head_text[0: empty_index]
        return obj_id

    # 找空包价格
    def find_empty_price(self, head_text):
        empty_index = self.find_enpty_index(head_text)
        by_index = self.find_by_index(head_text)
        empty_price = head_text[empty_index + 1: by_index]
        return empty_price

    # 找买单价格
    def find_by_price(self, head_text):
        by_index = self.find_by_index(head_text)
        by_price = head_text[by_index + 1:]
        return by_price

    def find_enpty_index(self, head_text):
        empty_index = head_text.find('空')
        return empty_index

    def find_by_index(self, head_text):
        by_index = head_text.find('买')
        return by_index


    # 解析字段名-首重
    def fixed_analysis_head(self, head_text):
        index_kg = head_text.find('kg')
        head_kg = head_text[0: index_kg]
        return head_kg

    # 解析字段名-续重
    def analysis_cte(self, head_text):
        weight = self.find_take_head_kg(head_text)  # 找打头阵重量
        unit = self.find_unit(head_text)  # 找单位
        carry = self.find_carry(head_text)  # 找进位
        return [weight, unit, carry]

    # 解析字段名-续重首重
    def analysis_cte_head(self, head_text):
        index_cte_head = head_text.find('续首')
        index_kg = head_text.find('kg')
        cte_head_kg = head_text[index_cte_head + 2: index_kg]
        return cte_head_kg

    # 解析浮动阶段字段名
    def float_cte_price(self, head_text):
        max_kg = self.find_max_kg(head_text)
        min_kg = self.find_min_kg(head_text)
        unit_price = self.find_unit(head_text)
        carry = self.find_carry(head_text)
        return [max_kg, min_kg, unit_price, carry]

    # 解析固定阶段字段名
    def fixed_cte_price(self, head_text):
        max_kg = self.find_take_head_kg(head_text)
        return max_kg

    # 找打头阵重量,或固定阶段最大重量
    def find_take_head_kg(self, head_text):
        index_kg = head_text.find('kg')
        head_kg = head_text[0: index_kg]
        return head_kg

    # 找浮动阶段最小重量
    def find_min_kg(self, head_text):
        index_sign = head_text.find('-')
        min_kg = head_text[0: index_sign - 2]
        return min_kg

    # 找浮动阶段最大重量
    def find_max_kg(self, head_text):
        index_sign = head_text.find('-')
        index_kg = head_text.find('kg', index_sign)
        max_kg = head_text[index_sign + 1: index_kg]
        return max_kg

    # 找单位
    def find_unit(self, head_text):
        index_unit = head_text.find('单')
        index_enter = head_text.find('进')
        unit_price = head_text[index_unit + 1: index_enter]
        return unit_price

    # 找进位
    def find_carry(self, head_text):
        index_enter = head_text.find('进')
        carry = head_text[index_enter + 1:]
        return carry

    # 解析西北超标备注
    def analysis_nw(self, head_text):
        proportion = self.find_proportion(head_text)
        price = self.find_unify_price(head_text)
        return [proportion, price]



    # 找超标比例
    def find_proportion(self, head_text):
        out_index = head_text.find('超')
        per_cent = head_text.find('%')
        proportion = head_text[out_index + 1: per_cent]
        return proportion


    # 找通票价格
    def find_unify_price(self, head_text):
        unify_index = head_text.find('通')
        price = head_text[unify_index + 1:]
        return price


    # 省份名转换
    def states_switch(self, pdata):
        try:
            pd_new = pd.DataFrame(pdata)
            nums = pd_new.shape[0]
            for i, j in zip(pd_new['省份'], range(nums)):
                if i == '广东':
                    pd_new.iloc[j, 0] = States.GUANGDONG_PROVINCE
                elif i == '上海':
                    pd_new.iloc[j, 0] = States.SHANGHAI
                elif i == '浙江':
                    pd_new.iloc[j, 0] = States.ZHEJIANG_PROVINCE
                elif i == '江苏':
                    pd_new.iloc[j, 0] = States.JIANGSU_PROVINCE
                elif i == '福建':
                    pd_new.iloc[j, 0] = States.FUJIAN_PROVINCE
                elif i == '江西':
                    pd_new.iloc[j, 0] = States.JIANGXI_PROVINCE
                elif i == '广西':
                    pd_new.iloc[j, 0] = States.GUANGXIZHUANG_AUTONOMOUS_REGION
                elif i == '湖南':
                    pd_new.iloc[j, 0] = States.HUNAN_PROVINCE
                elif i == '湖北':
                    pd_new.iloc[j, 0] = States.HUBEI_PROVINCE
                elif i == '安徽':
                    pd_new.iloc[j, 0] = States.ANHUI_PROVINCE
                elif i == '海南':
                    pd_new.iloc[j, 0] = States.HAINAN_PROVINCE
                elif i == '山东':
                    pd_new.iloc[j, 0] = States.SHANDONG_PROVINCE
                elif i == '河南':
                    pd_new.iloc[j, 0] = States.HENAN_PROVINCE
                elif i == '山西':
                    pd_new.iloc[j, 0] = States.SHANXI_PROVINCE
                elif i == '四川':
                    pd_new.iloc[j, 0] = States.SICHUAN_PROVINCE
                elif i == '陕西':
                    pd_new.iloc[j, 0] = States.SHANXI_PROVINCE
                elif i == '河北':
                    pd_new.iloc[j, 0] = States.HEBEI_PROVINCE
                elif i == '云南':
                    pd_new.iloc[j, 0] = States.YUNNAN_PROVINCE
                elif i == '贵州':
                    pd_new.iloc[j, 0] = States.GUIZHOU_PROVINCE
                elif i == '辽宁':
                    pd_new.iloc[j, 0] = States.LIAONING_PROVINCE
                elif i == '吉林':
                    pd_new.iloc[j, 0] = States.JILIN_PROVINCE
                elif i == '黑龙江':
                    pd_new.iloc[j, 0] = States.HEILONGJIANG_PROVINCE
                elif i == '内蒙古':
                    pd_new.iloc[j, 0] = States.THE_NEIMONGGOL_AUTONOMOUS_REGION
                elif i == '甘肃':
                    pd_new.iloc[j, 0] = States.GANSU_PROVINCE
                elif i == '宁夏':
                    pd_new.iloc[j, 0] = States.THE_NINGXIA_AUTONOMOUS_REGION
                elif i == '青海':
                    pd_new.iloc[j, 0] = States.QINGHAI_PROVINCE
                elif i == '新疆':
                    pd_new.iloc[j, 0] = States.THE_XINJIANG_UYGUR_AUTONOMOUS_REGION
                elif i == '西藏':
                    pd_new.iloc[j, 0] = States.GUANGXIZHUANG_AUTONOMOUS_REGION
            return pd_new
        except Exception:
            return False

    # 获取报价
    def get_price(self, objid):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = QueryDataByBase.get_pricebyobjid(objid)
        cur.execute(sql)
        data = cur.fetchall()
        cur.close()
        conn.close()
        return pd.DataFrame(data)

    # 获取西北超标报价
    def get_nw_price(self, objid):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = QueryDataByBase.get_nw_pricebyobjid(objid)
        cur.execute(sql)
        data = cur.fetchall()
        cur.close()
        conn.close()
        return pd.DataFrame(data)


    # 创建文件目录,如有同名目录不创建,没有同名目录就创建目录
    def Creating_file_Directory(self):
        try:
            path = ExportExcelPaths.OFFER_TABLE
            if not os.path.exists(path):
                os.makedirs(path)
            return True
        except Exception:
            return False

    # 打开文件目录
    def open_files(self):
        try:
            path = ExportExcelPaths.OFFER_TABLE
            os.startfile(path)
            return True
        except Exception:
            return False

    # 导出报价为excel
    def sql_export_excel(self):
        try:
            self.Creating_file_Directory()
            datax = MessagesData.SET_PRICE
            df = pd.DataFrame(datax)
            df.rename(columns={'id': '序号',
                               'obj_id': '客户工号',
                               'states': '省份',
                               'head_kg': '首重',
                               'stage_min': '阶段最小重量',
                               'stage_max': '阶段最大重量',
                               'head_price': '首重价格',
                               'cte_kg': '阶段浮动计费',
                               'cte_fixed': '阶段固定计费',
                               'cte_unit': '计算单位',
                               'carry': '进位',
                               'store': '店铺'}, inplace=True)
            obj_num = df.iloc[0, 1]
            df.to_excel(ExportExcelPaths.OFFER_TABLE +
                        ExportExcelPaths.EXPORT_EXCEL_HEAD_NAME +
                        str(obj_num) + "__" +
                        CommonPaths.DIR_TIMESTAMP +
                        FormatPaths.XLSX_FORMAT, index=False)
            return True
        except Exception:
            return False

    # 导出报价为excel
    def nwsql_export_excel(self):
        try:
            self.Creating_file_Directory()
            datax = MessagesData.SET_NW_PRICE
            df = pd.DataFrame(datax)
            df.rename(columns={'w_id': '序号',
                               'objw_id': '客户工号',
                               'proportion': '比例',
                               'head_kg': '首重',
                               'stage_min': '阶段最小重量',
                               'stage_max': '阶段最大重量',
                               'head_price': '首重价格',
                               'cte_kg': '阶段浮动计费',
                               'cte_fixed': '阶段固定计费',
                               'cte_unit': '计算单位',
                               'carry': '进位',
                               'store': '店铺'}, inplace=True)
            obj_num = df.iloc[0, 1]
            df.to_excel(ExportExcelPaths.OFFER_TABLE +
                        ExportExcelPaths.EXPORT_EXCEL_HEAD_NAME +
                        str(obj_num) + "__" +
                        CommonPaths.DIR_TIMESTAMP +
                        FormatPaths.XLSX_FORMAT, index=False)
            return True
        except Exception:
            return False

    # 删除对应工号相关的所有表或数据
    def del_price_table(self, obj_id):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        try:
            sql0 = QueryDataByBase.del_pricetablebyobjid(obj_id)
            sql1 = QueryDataByBase.del_pricedatabyobjid(obj_id)
            if_data = self.if_nw_table(obj_id)
            if if_data == 0:
                sql2 = QueryDataByBase.del_pricenwtablebyobjid(obj_id)
                cur.execute(sql2)
            cur.execute(sql0)
            cur.execute(sql1)
            conn.commit()
            cur.close()
            conn.close()
            return True
        except Exception:
            cur.close()
            conn.close()
            return False

    # 判断是否存在西北报价表
    def if_nw_table(self, obj_id):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = QueryDataByBase.query_havetable_quotation(obj_id)
        cur.execute(sql)
        data = cur.fetchall()
        data = pd.DataFrame(data)
        data = data.iloc[0, 0]
        cur.close()
        conn.close()
        return data

    # 确认密码
    def affirm_password(self, password):
        try:
            if str(password) == str(MessagesUsers.PASS_WORD):
                return 1
            else:
                return -1
        except Exception:
            return False


    # 查看模板图片
    def look_photo(self):
        img = cv2.imread("images/mode1.jpg")
        cv2.imshow("img", img)

    # 通过工号获取 空包，买单，等信息
    def get_empty_unit_price_data(self, obj_id):
        conn = self.creat_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = QueryDataByBase.get_pricedatabyobjid(obj_id)
        cur.execute(sql)
        data = cur.fetchall()
        data = pd.DataFrame(data)
        cur.close()
        conn.close()
        return data